package org.kev.transit;


import android.content.ContentProvider;
import android.content.DatabaseContentProvider;

import android.content.ContentValues;

import android.content.Resources;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;
import java.util.HashMap;

import org.kev.transit.data.Direction;
import org.kev.transit.data.Route;
import org.kev.transit.data.RouteType;
import org.kev.transit.data.Stop;
import org.kev.transit.data.TimeTable;
import org.kev.transit.util.TransitUtil;


/**
 * Provides access to a database of transportation data.
 */
public class TransitProvider extends ContentProvider {
    
    private static SQLiteDatabase mDB;
    private static final String TAG = "TransitProvider";
    private static final String DATABASE_NAME = "transit.db";
    public static final String AUTH = "transit";
    private static final int DATABASE_VERSION = 2;
    public static HashMap<String, String> TIMES_PROJECTION_MAP;
	//private static HashMap<String, String> TIMES_LIST_PROJECTION_MAP;
    private static final int TIMES = 1;
    private static final int TIMES_ID = 2;
    private static final int ROUTE_TYPE = 3;

    private static final int ROUTE = 5;
  
    private static final int DIRECTION = 7;

    private static final int STOPS = 9;
    private static final int STOPS_ID = 10;
    
    
    private static final UriMatcher URL_MATCHER;

/**
 * 
 * @author kevin
 *
 */
    private static class DatabaseHelper extends SQLiteOpenHelper {

        @Override
        /**
         *  create database event
         */
        public void onCreate(SQLiteDatabase db) {
        	Log.d(TAG,"onCreate="+db);
        	Log.d(TAG,"DROP TABLE");
        	db.execSQL("DROP TABLE IF EXISTS time_table ");
        	db.execSQL("DROP TABLE IF EXISTS route ");
        	db.execSQL("DROP TABLE IF EXISTS route_type ");
        	db.execSQL("DROP TABLE IF EXISTS direction ");
        	db.execSQL("DROP TABLE IF EXISTS stop ");
       
        	Log.d(TAG,"+++++++++++create TABLE++++++++++++++++++");
            String sql="CREATE TABLE settings ("+
                       " _ID  INTEGER PRIMARY KEY,"+
                       " SYSTEM TEXT,"+
                       " URL TEXT"+
                       " CREATE_DATE INTEGER"+
                       ");"; 
            Log.d(TAG,sql);
            db.execSQL(sql);
            /* lookup tables */
            sql="CREATE TABLE route_type ("+
            		   RouteType._ID+ " INTEGER PRIMARY KEY, "+
            		   RouteType.ROUTE_TYPE_ID+ " ,"+
            		   RouteType.TITLE+" TEXT, "+ 
                       RouteType.CREATE_DATE+" INTEGER"+                       
                       ");"; 
            Log.d(TAG,sql);
            db.execSQL(sql);
            sql="CREATE TABLE route ("+
            		Route._ID+" INTEGER PRIMARY KEY, "+
            		Route.ROUTE_ID+" INTEGER, "+
            		Route.ROUTE_TYPE+" INTEGER, "+  
            		Route.TITLE+" TEXT, "+
            		Route.CREATE_DATE+" INTEGER,"+
            		" UNIQUE("+
              		Route.ROUTE_ID+","+Route.ROUTE_TYPE+")"+
              	              		   ") ;";
            Log.d(TAG,sql);
            db.execSQL(sql);
            sql="CREATE TABLE direction ("
            		   +Direction._ID+" INTEGER PRIMARY KEY, "
            		   +Direction.DIRECTION_ID+" INTEGER , "
                       +Direction.ROUTE_TYPE+" INTEGER, "
                       +Direction.ROUTE+" INTEGER, "
                       +Direction.CREATE_DATE+" INTEGER, "
                       +Direction.TITLE+" TEXT, "
                       +" UNIQUE ("                      
                       +Direction.DIRECTION_ID+","+Direction.ROUTE_TYPE+","+Direction.ROUTE+") )";
                        
            Log.d(TAG,sql);
            db.execSQL(sql);
            
            sql="CREATE TABLE stop ("
            	+Stop._ID+  " INTEGER PRIMARY KEY, "
            	+Stop.STOP_ID+  " INTEGER,"
                +Stop.ROUTE_TYPE+  " INTEGER,"
                +Stop.ROUTE+       " INTEGER,"
                +Stop.DIRECTION+   " INTEGER,"
                +Stop.TITLE+" TEXT, "
                +Stop.LAT+  " TEXT, "
                +Stop.LON+  " TEXT, "
            	+Stop.CREATE_DATE+" INTEGER, "
                +" UNIQUE ("                      
                +Stop.STOP_ID+","+Stop.ROUTE_TYPE+","+Stop.ROUTE+","+Stop.DIRECTION+") )";

     Log.d(TAG,sql);
     db.execSQL(sql);

            //main table     	
      sql= "CREATE TABLE time_table ("
        		+TimeTable._ID+         " INTEGER  PRIMARY KEY,"
        		+TimeTable.TIME_ID+         " INTEGER,"
                +TimeTable.ROUTE_TYPE+  " INTEGER,"
                +TimeTable.ROUTE+       " INTEGER,"
                +TimeTable.DIRECTION+   " INTEGER,"
                +TimeTable.STOP_ID+     " INTEGER,"
                +TimeTable.TIME+        " TEXT, "
                +TimeTable.MODIFIED+ " INTEGER, "
                +" UNIQUE ("                      
                +TimeTable.TIME_ID+","+TimeTable.ROUTE_TYPE+","+TimeTable.ROUTE+","
                +TimeTable.DIRECTION+","+TimeTable.STOP_ID
                +") )";
	    	Log.d(TAG,sql);
	    	db.execSQL(sql);
	
        }

        @Override
        /**
         * upgrade Database event
         */
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS time_table ");
        	db.execSQL("DROP TABLE IF EXISTS route ");
        	db.execSQL("DROP TABLE IF EXISTS toute_type ");
        	db.execSQL("DROP TABLE IF EXISTS direction ");
        	db.execSQL("DROP TABLE IF EXISTS stop ");
        	db.execSQL("DROP TABLE IF EXISTS settings ");
        	
            onCreate(db);
        }
    }
    
    @Override
    /**
     * create DB event
     */
    public boolean onCreate() {
    	Log.d(TAG,"onCreate");
        DatabaseHelper dbHelper = new DatabaseHelper();
        mDB = dbHelper.openDatabase(getContext(), DATABASE_NAME, null, DATABASE_VERSION);
        Log.d(TAG,"open mDB="+mDB);
        return (mDB == null) ? false : true;
    }

    @Override
    /**
     * query database 
     */
    public Cursor query(Uri url, String[] projection, String selection,
            String[] selectionArgs, String sort) {
    	SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        String groupBy=null;
        //Log.d(TAG,"query url="+url);
        //Log.d(TAG,"query url match="+URL_MATCHER.match(url));
        //Log.d(TAG,"urls" +URL_MATCHER.toString());
        
        String orderBy="";
    		

        switch (URL_MATCHER.match(url)) {
        case STOPS:
            qb.setTables(Stop.TABLE_NAME);
            
            // groupBy=Stop.TITLE;
           /*do not sort! this way I can display then in order
            * so the select direction's station should always be on top
            *    if (TextUtils.isEmpty(sort)) {
                orderBy = Stop.DEFAULT_SORT_ORDER;
            } else {
                orderBy = sort;
            }
            */          
            break;
        case STOPS_ID:
                qb.setTables(Stop.TABLE_NAME);
                qb.appendWhere("id=" + url.getLastPathSegment() );          
                break;
        case TIMES:
            qb.setTables(TimeTable.TABLE_NAME );
            //qb.setProjectionMap(TIMES_LIST_PROJECTION_MAP);
            //qb.setDistinct(true);
            if (TextUtils.isEmpty(sort)) {
                orderBy = TimeTable.DEFAULT_SORT_ORDER;
            } else {
                orderBy = sort;
            }          
            break;

        case TIMES_ID:
            qb.setTables(TimeTable.TABLE_NAME);
            qb.appendWhere("id=" + url.getLastPathSegment());
            break;
        case ROUTE_TYPE:
            qb.setTables(RouteType.TABLE_NAME);
            /*
            if (TextUtils.isEmpty(sort)) {
                orderBy = RouteType.DEFAULT_SORT_ORDER;
            } else {
                orderBy = sort;
            }
            */
            //groupBy = TimeTable.ROUTE_TYPE;
            //qb.setProjectionMap(TIMES_LIST_PROJECTION_MAP);
           // qb.appendWhere("id=" + url.getPathSegment(10));
            break;
        case ROUTE:
            qb.setTables(Route.TABLE_NAME);
            groupBy = Route.TITLE;
            /* 
            if (TextUtils.isEmpty(sort)) {
                orderBy = Route.DEFAULT_SORT_ORDER;
            } else {
                orderBy = sort;
            }
            */
            break;           
        case DIRECTION:
            qb.setTables(Direction.TABLE_NAME);
            groupBy = Direction.TITLE;
           /* if (TextUtils.isEmpty(sort)) {
                orderBy = Direction.DEFAULT_SORT_ORDER;
            } else {
                orderBy = sort;
            }*/        
      
            break;           
        default:
            throw new IllegalArgumentException("Unknown URL " + url);
        }

        // If no sort order is specified use the default

        Log.d(TAG,"projection="+projection);
        Log.d(TAG,"query="+qb.toString());
        Log.d(TAG,"where ="+selection);
        Log.d(TAG,"args ="+TransitUtil.dump(selectionArgs));
        Log.d(TAG,"group ="+groupBy);
        
        Cursor c = qb.query(mDB, projection, selection, selectionArgs, groupBy,
                null, orderBy);
        Log.d(TAG,"cursor rows="+c.count()+"cols"+c.getColumnNames().length);
        
        c.setNotificationUri(getContext().getContentResolver(), url);
        return c;
    }

    @Override
    /**
     * get Type
     */
    public String getType(Uri url) {
    	
        switch (URL_MATCHER.match(url)) {
        case TIMES:
            return "vnd.android.cursor.dir/vnd.org.kev.timeTable";
        case TIMES_ID:
            return "vnd.android.cursor.item/vnd.google.timeTableId";

        default:
            throw new IllegalArgumentException("Unknown URL getType()=" + url);
        }
    }
    @Override
    /**
     * insert
     */
    public Uri insert(Uri url, ContentValues initialValues) {
        long rowID;
        ContentValues values=null;
        if (initialValues != null) {
            values = new ContentValues(initialValues);
        } else {
            values = new ContentValues();
        }
        Log.d(TAG,"insert URl="+url);
     //   Long now = Long.valueOf(System.currentTimeMillis());
        Resources r = Resources.getSystem();
        
        switch (URL_MATCHER.match(url)) {
        
        case TIMES:
        	return insertTimes(url,initialValues,values);
        case DIRECTION:
        	Log.d(TAG,"iserert Direct CASE!");
            if (values.containsKey(Direction.DIRECTION_ID) == false) {
                values.put(Direction.DIRECTION_ID, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Direction.TITLE) == false) {
                values.put(Direction.TITLE, r.getString(android.R.string.untitled));

            }
            if (values.containsKey(Direction.ROUTE) == false) {
                values.put(Direction.ROUTE, r.getString(android.R.string.untitled));

            }
            if (values.containsKey(Direction.ROUTE_TYPE) == false) {
                values.put(Direction.ROUTE_TYPE, r.getString(android.R.string.untitled));

            }
            rowID = mDB.insert(Direction.TABLE_NAME, Direction.TABLE_NAME, values);
            //rowId = mDB.i
            if (rowID > 0) {
              
                getContext().getContentResolver().notifyChange(url, null);
                return url;
            }
        break;
        case ROUTE_TYPE:
            if (values.containsKey(RouteType.ROUTE_TYPE_ID) == false) {
                values.put(RouteType.ROUTE_TYPE_ID, r.getString(android.R.string.untitled));

            }
            if (values.containsKey(RouteType.TITLE) == false) {
                values.put(RouteType.TITLE, r.getString(android.R.string.untitled));
            }
            Log.d(TAG,"values="+values);
            Log.d(TAG,"mDB="+mDB);
            rowID = mDB.insert(RouteType.TABLE_NAME, RouteType.TABLE_NAME, values);
            //rowId = mDB.i
            if (rowID > 0) {
               
                getContext().getContentResolver().notifyChange(url, null);
                return url;
            }
        break;
        case ROUTE:
        	Log.d(TAG,"+++insert route");
            if (values.containsKey(Route.ROUTE_ID) == false) {
                values.put(Route.ROUTE_ID, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Route.TITLE) == false) {
                values.put(Route.TITLE, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Route.ROUTE_TYPE) == false) {
                values.put(Route.ROUTE_TYPE, r.getString(android.R.string.untitled));
            }
            
            //Log.d(TAG,"+++insert route"+Route.TABLE_NAME+","+Route.TABLE_NAME+values);
            rowID = mDB.insert(Route.TABLE_NAME, Route.TABLE_NAME, values);
            //rowId = mDB.i
            if (rowID > 0) {
                
                getContext().getContentResolver().notifyChange(url, null);
                return url;
            }
        break;
        case STOPS:
        	Log.d(TAG,"+++insert stop(station");
            if (values.containsKey(Stop.STOP_ID) == false) {
                values.put(Stop.STOP_ID, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Stop.TITLE) == false) {
                values.put(Route.TITLE, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Stop.LAT) == false) {
                values.put(Stop.LAT, r.getString(android.R.string.untitled));
            }
            if (values.containsKey(Stop.LON) == false) {
                values.put(Stop.LON, r.getString(android.R.string.untitled));
            }
            
            Log.d(TAG,"+++insert stop"+Stop.TABLE_NAME+","+values);
            rowID = mDB.insert(Stop.TABLE_NAME, Stop.TABLE_NAME, values);
           Log.d(TAG,"row id="+rowID);
            if (rowID > 0) {
                
                getContext().getContentResolver().notifyChange(url, null);
                return url;
            }
        
        	
        	break;
        
        default:
	    	Log.e(TAG,"Unknown URL auth=" + url.getAuthority()+"path="+url.getPath()+"url="+url);
        	throw new IllegalArgumentException("Unknown URL auth=" + url.getAuthority()+"path="+url.getPath()+"url="+url);     
        }
        throw new SQLException("Failed to insert row into " + url);        
     }
    
    @Override
    /**
     * delete
     */
    public int delete(Uri url, String where, String[] whereArgs) {
    	Log.d(TAG,">>> delete="+url);
    	Log.d(TAG,"where="+where);
    	Log.d(TAG,"args="+TransitUtil.dump(whereArgs));
        int count;
        int rowId = 0;
        switch (URL_MATCHER.match(url)) {
        case TIMES:
            count = mDB.delete(TimeTable.TABLE_NAME, where, whereArgs);
            break;

        case TIMES_ID:
            String segment = url.getPathSegments().get(1);
            rowId = Integer.parseInt(segment);
            count = mDB.delete(TimeTable.TABLE_NAME,TimeTable._ID+"="+ segment+ (!TextUtils.isEmpty(where) ? " AND (" +
            		where + ')' : ""), whereArgs);
            Log.d(TAG,"<<< delete row_id="+count);

            return rowId;
        case STOPS:
        	count=mDB.delete(Stop.TABLE_NAME, where, whereArgs);
            break;
        case ROUTE:
        	count=mDB.delete(Route.TABLE_NAME, where, whereArgs);
            break;
        case ROUTE_TYPE:
        	count=mDB.delete(RouteType.TABLE_NAME, where, whereArgs);
            break;
        case DIRECTION:
        	count=mDB.delete(Direction.TABLE_NAME, where, whereArgs);
            break;            
        default:
            throw new IllegalArgumentException("Unknown URL " + url);
        }

        getContext().getContentResolver().notifyChange(url, null);
        Log.d(TAG,"<<< delete="+count);
        return count;
    }

    @Override
    /**
     * update
     */
    public int update(Uri url, ContentValues values, String where, String[] whereArgs) {
        int count;
        Log.d(TAG,">>>update url="+url);
        switch (URL_MATCHER.match(url)) {
        case TIMES:
            count = mDB.update(TimeTable.TABLE_NAME, values, where, whereArgs);
        break;
        case STOPS:
            count = mDB.update(Stop.TABLE_NAME, values, where, whereArgs);
        break;

        case ROUTE:
            count = mDB.update(Route.TABLE_NAME, values, where, whereArgs);
        break;

        case ROUTE_TYPE:
            count = mDB.update(RouteType.TABLE_NAME, values, where, whereArgs);
        break;

        case DIRECTION:
            count = mDB.update(Direction.TABLE_NAME, values, where, whereArgs);
        break;

       /* case TIMES_ID:
            String segment = url.getPathSegment(1);
            count = mDB
                    .update("notes", values, "_id="
                            + segment
                            + (!TextUtils.isEmpty(where) ? " AND (" + where
                                    + ')' : ""), whereArgs);
        break;
        */	
        default:
            throw new IllegalArgumentException("Unknown URL " + url);
        }

        getContext().getContentResolver().notifyChange(url, null);
        Log.d(TAG,"<<< update url="+count);
        return count;
    }
    /**
     * 
     * @param url
     * @param initialValues
     * @param values
     * @return
     * @throws SQLException
     */
    private Uri insertTimes(Uri url, ContentValues initialValues,ContentValues values) throws SQLException{
        Long now = Long.valueOf(System.currentTimeMillis());
        Resources r = Resources.getSystem();

        // Make sure that the fields are all set
        if (values.containsKey(TimeTable.MODIFIED) == false) {
            values.put(TimeTable.MODIFIED, now);
        }

        if (values.containsKey(TimeTable.STOP_ID) == false) {
            values.put(TimeTable.STOP_ID, r.getString(android.R.string.untitled));
        }

        if (values.containsKey(TimeTable.DIRECTION) == false) {
            values.put(TimeTable.DIRECTION,  r.getString(android.R.string.untitled));
        }
        if (values.containsKey(TimeTable.ROUTE_TYPE) == false) {
            values.put(TimeTable.ROUTE_TYPE,  r.getString(android.R.string.untitled));
        }
        long rowID = mDB.insert(TimeTable.TABLE_NAME, "time", values);
        Log.d(TAG,"rowId="+rowID);
        if (rowID > 0) {
           
            getContext().getContentResolver().notifyChange(url, null);
            Log.d(TAG,"URL="+url);
            return url;
        }

        throw new SQLException("Failed to insert row into " + url);

    }
    
    static {
        URL_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
        Log.d(TAG,"---------------------------------------------------" );
        Log.d(TAG,"url="+TimeTable.CONTENT_URI.toString() );
        Log.d(TAG,"auth="+TimeTable.CONTENT_URI.getAuthority()+"path="+TimeTable.CONTENT_URI.getPath() );
        
        URL_MATCHER.addURI(AUTH, "time_table", TIMES);
        URL_MATCHER.addURI(AUTH, "time_table"+"/#", TIMES_ID);
        URL_MATCHER.addURI(AUTH, "route_type", ROUTE_TYPE);
        
        URL_MATCHER.addURI(AUTH, "route", ROUTE);
        URL_MATCHER.addURI(AUTH, "direction", DIRECTION);
        URL_MATCHER.addURI(AUTH, "stop", STOPS);
        URL_MATCHER.addURI(AUTH, "stop"+"/#", STOPS_ID);
        
        /*not used currently.  Will use it at later date.
         
        TIMES_LIST_PROJECTION_MAP = new HashMap<String, String>();
        TIMES_LIST_PROJECTION_MAP.put(TimeTable._ID, TimeTable._ID);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.TIME_ID, TimeTable._ID);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.TIME, TimeTable.TIME);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.ROUTE, TimeTable.ROUTE);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.ROUTE_TYPE, TimeTable.ROUTE_TYPE);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.STOP_ID, TimeTable.ROUTE_TYPE);
        TIMES_LIST_PROJECTION_MAP.put(Stop.TITLE, Stop.TITLE);
        TIMES_LIST_PROJECTION_MAP.put(Stop.LAT, Stop.LAT);
        TIMES_LIST_PROJECTION_MAP.put(Stop.LON, Stop.LON);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.DIRECTION, TimeTable.DIRECTION);
        TIMES_LIST_PROJECTION_MAP.put(TimeTable.MODIFIED, TimeTable.MODIFIED);
        */
    }


}
